﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using LotteryVote.Entity;
using System.Data.SqlClient;

namespace LotteryVote.Data
{
    public class BetLimitDataAccess : DataBase
    {
        public void Insert(BetLimit limit)
        {
            string sql = string.Format(@"INSERT INTO {0} ({1},{2},{3},{4}) VALUES (@{1},@{2},@{3},@{4})",
                BetLimit.TABLENAME, BetLimit.USERID, BetLimit.GAMEID, BetLimit.LEASTLIMIT, BetLimit.LARGESTLIMIT);
            base.ExecuteNonQuery(sql, new SqlParameter(BetLimit.USERID, limit.UserId),
                new SqlParameter(BetLimit.GAMEID, limit.GameId),
                new SqlParameter(BetLimit.LEASTLIMIT, limit.LeastLimit),
                new SqlParameter(BetLimit.LARGESTLIMIT, limit.LargestLimit));
        }
        public void Delete(int userId)
        {
            string sql = string.Format(@"DELETE {0} WHERE {1}=@{1}", BetLimit.TABLENAME, BetLimit.USERID);
            base.ExecuteNonQuery(sql, new SqlParameter(BetLimit.USERID, userId));
        }

        public IList<BetLimit> ListLimitByUser(int userId)
        {
            string sql = string.Format(@"SELECT * FROM {0} WHERE {1}=@{1}", BetLimit.TABLENAME, BetLimit.USERID);
            return base.ExecuteList<BetLimit>(sql, new SqlParameter(BetLimit.USERID, userId));
        }
        public IList<BetLimit> GetParentLimit(int childUserId)
        {
            string sql = string.Format(@"SELECT BL.* FROM {0} BL
JOIN (SELECT UR.* FROM {1} AS UR,(SELECT * FROM {1} WHERE {2}=@{2}) AS T
WHERE UR.{2}=T.{3}) TU ON TU.{2}=BL.{4}", BetLimit.TABLENAME, User.TABLENAME, User.USERID, User.PARENTID, BetLimit.USERID);
            return base.ExecuteList<BetLimit>(sql, new SqlParameter(User.USERID, childUserId));
        }
    }
}
